package myauctioneer.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import myauctioneer.App;
import myauctioneer.dto.Article;
import myauctioneer.dto.Rating;

public class RatingDAO implements RatingDAOI {

	private PreparedStatement saveStmt;
	private PreparedStatement loadStmt1;
	private PreparedStatement loadStmt2;
	private PreparedStatement loadStmt3;

	public RatingDAO() {
		try {
			saveStmt = App.getDb().prepareStatement(
					"INSERT INTO rating(customer_id, item_id, rating_detail_id, type) "
							+ "VALUES (?, ?, ?, ?)");
			loadStmt1 = App.getDb().prepareStatement(
					"SELECT * FROM rating " + "WHERE rating_id = ?");
			loadStmt2 = App
					.getDb()
					.prepareStatement(
							"SELECT r.rating_id, r.customer_id, r.item_id, r.rating_detail_id FROM rating r INNER JOIN item i ON r.item_id = i.item_id INNER JOIN customer c ON i.customer_id = c.customer_id WHERE c.customer_id = ? AND r.type = ?");
			loadStmt3 = App
					.getDb()
					.prepareStatement(
//old							"SELECT r.rating_id, r.customer_id, r.item_id, r.type, r.rating_detail_id FROM rating r INNER JOIN item i ON r.item_id = i.item_id INNER JOIN bid b ON i.final_bid = b.bid_id INNER JOIN customer c ON b.customer_id = c.customer_id WHERE c.customer_id = ?");
							"SELECT r.rating_id, r.customer_id, r.item_id, r.type, r.rating_detail_id FROM rating r INNER JOIN item i ON r.item_id = i.item_id INNER JOIN bid b ON i.final_bid = b.bid_id INNER JOIN customer c ON b.customer_id = c.customer_id WHERE c.customer_id = ? AND r.type = ?");//simon, new

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	public void saveRating(Rating r) {
		try {
			saveStmt.setInt(1, r.getCustomerId());
			saveStmt.setInt(2, r.getItemId());
			saveStmt.setInt(3, r.getRatingDetailId());
			saveStmt.setString(4, r.getType());
			saveStmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	public Rating loadRatingByRatingId(int ratingId) {
		Rating r = new Rating();
		try {
			loadStmt1.setInt(1, ratingId);
			ResultSet result = loadStmt1.executeQuery();
			if (!result.next())
				return null;

			r.setRatingId(result.getInt("rating_id"));
			r.setCustomerId(result.getInt("customer_id"));
			r.setItemId(result.getInt("item_id"));
			r.setRatingDetailId(result.getInt("rating_detail_id"));
			r.setType(result.getString("type"));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return r;
	}

	@Override
	public ArrayList<Rating> loadRatingsByUserIdAndType(int userId, String type) {
		ArrayList<Rating> rAL = new ArrayList<Rating>();
		try {
			loadStmt2.setInt(1, userId);
			loadStmt2.setString(2, type);
			ResultSet result = loadStmt2.executeQuery();

			while (result.next())
				rAL.add(this.loadRatingByRatingId(result.getInt("rating_id")));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rAL;

	}

	@Override
	public boolean deleteRatingById(int userId) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
//old	public ArrayList<Rating> loadRatingsByBuyerId(int buyerId) {
	public ArrayList<Rating> loadRatingsByBuyerId(int buyerId, String type) { //simon, new
		ArrayList<Rating> rAL = new ArrayList<Rating>();
		try {
			loadStmt3.setInt(1, buyerId);
			loadStmt3.setString(2, type);//simon, new
			ResultSet result = loadStmt3.executeQuery();

			while (result.next())
				rAL.add(this.loadRatingByRatingId(result.getInt("rating_id")));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rAL;
	}

}
